# coding: utf8
from ops.hades.models import Instance, Query
from pymysql import connect


class SysConfig(object):
    @staticmethod
    def get_config(id, db=None):
        """
        根据id获取配置，返回数据库地址 账号及密码
        :return:
        """
        config = {}
        print('id is %s' %id)
        # 根据id查询数据库
        orgin_config = Instance.objects.values().filter(id=id)
        print(orgin_config)
        if len(orgin_config):
            data = orgin_config[0]
            config['host'] = data.get('host')
            config['port'] = data.get('port')
            config['user'] = data.get('user')
            config['passwd'] = data.get('passwd')
            config['db'] = db

        print(config)
        return config


class Engines(object):
    @classmethod
    def get_engine(cls, db_type):
        # db_type = data.get('db_type')
        if db_type == 'mysql':
            engine = MysqlEngine
            return engine
        elif db_type == 'redis':
            pass
        elif db_type == 'mongo':
            pass
        else:
            return '无效的db_type，请检查!'


class MysqlEngine(object):
    @classmethod
    def conn(cls, id, db):
        config = SysConfig.get_config(id, db)
        host = config.get('host')
        port = config.get('port', 3306)
        user = config.get('user', 'root')
        passwd = config.get('passwd')
        db = config.get('db')
        print(db)

        if not (host or user or passwd):
            return {'errCode': 500, 'msg': '获取host|user|passwd失败，请检查'}

        if db:
            my_conn = connect(host=host,
                              user=user,
                              password=passwd,
                              database=db,
                              charset='utf8mb4')
        else:
            my_conn = connect(host=host,
                              user=user,
                              password=passwd,
                              charset='utf8mb4')

        return my_conn

    @classmethod
    # def exec(cls, instance_id, instance_resource, resource, sql):
    def exec(cls, *args, **kwargs):
        """
        database和sql存在则直接连接db并执行sql;
        只存在database和table则返回表结构；
        只存在database则返回databases名字
        :param args:
        :param kwargs:
        :return:
        """
        # 存在sql就执行sql
        instance_id = kwargs.get('instance_id')
        database = kwargs.get('database')
        #table = kwargs.get('table')
        query_sql = kwargs.get('query_sql')

        if not instance_id and database:
            msg = '数据库实例id或库未指定，请检查'
            return msg

        # 未传入database则获取database并返回
        if not database:
            conn = cls.conn(instance_id[0], db=None)
            query_sql = 'show databases;'
            cursor = conn.cursor()
            cursor.execute(query_sql)
            conn.commit()
            dbs = cursor.fetchall()
            print(dbs)
            rows = [db[0] for db in dbs if db]
            column_list = ["Database"]
        else:
            # 动作为query时，返回数据并写入日志
            conn = cls.conn(instance_id, database)
            cursor = conn.cursor()
            cursor.execute(query_sql)
            conn.commit()
            res = cursor.fetchall()
            rows = [list(r) for r in res]
            column_list = [c[0] for c in cursor.description]
        conn.close()

        data = {
            'rows': rows,
            "column_list": column_list
        }
        result = {
                    'code': 2000,
                    'data': {
                        'data': data
                    }
        }
        return result





